package mx.edu.itesm.tps.proyecto.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import mx.edu.itesm.tps.proyecto.pojo.UserExam;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.webflow.action.FormAction;

public class UserExamDao extends FormAction implements ProjectEventDao {

    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertCourse;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertCourse = new SimpleJdbcInsert(dataSource).withTableName("courses");
    }

    public List getGrades() {
        return simpleJdbcTemplate.queryForList("SELECT * FROM user_exam ORDER BY name", new Object[]{});
    }

    public List getGradesByAlumId(String id) {
        return simpleJdbcTemplate.queryForList("SELECT u.id, u.user, u.examid, u.grade, e.name AS examname FROM user_exam AS u, exams AS e WHERE u.user = ? AND u.examid = e.id ORDER BY grade", new Object[]{id});
    }

    public void add(UserExam user) {
        Map<String, Object> parameters = new HashMap<String, Object>(UserExam.class.getDeclaredFields().length);
        parameters.put("id", user.getId());
        parameters.put("user", user.getUser());
        parameters.put("examid", user.getExamid());
        parameters.put("grade", user.getGrade());

        insertCourse.execute(parameters);
    }

    private class UserExamMapper implements ParameterizedRowMapper<UserExam> {

        public UserExam mapRow(ResultSet rs, int rowNum) throws SQLException {
            UserExam user = new UserExam();
            user.setId(rs.getInt("id"));
            user.setUser(rs.getString("user"));
            
            try {
                user.setExamName(rs.getString("examname"));
            } catch (Exception e) {}
            
            user.setExamid(rs.getInt("examid"));
            user.setGrade(rs.getInt("grade"));
            
            return user;
        }
    }
}
